from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel, Field
from typing import List, Optional
from datetime import datetime
import logging
from util.sql_pyodbc import SQLServer_pyodbc
import pyodbc

# 创建数据库连接
database = SQLServer_pyodbc("AnJiServer")

router = APIRouter(
    prefix="/api/temp-brand-models",
    tags=["temp-brand-models"]
)


class TempBrandModel(BaseModel):
    contract_no: str
    picking_id: str
    material_code: str
    brand: str
    model: str
    specification: str
    origin: Optional[str] = ""  # 设置为可选字段，默认为空字符串
    quantity: int


class TempBrandModelResponse(TempBrandModel):
    id: int
    created_at: datetime
    last_modified: datetime


@router.post("/", response_model=dict)
async def save_temp_brand_model(model: TempBrandModel):
    try:
        # 确保origin字段不为None
        if model.origin is None:
            model.origin = ""
            
        # 打印接收到的数据，便于调试
        logging.info(f"接收到的数据: {model.dict()}")
        
        # 创建新记录
        insert_sql = """
            INSERT INTO TempBrandModels 
            (ContractNo, PickingID, MaterialCode, Brand, Model, Specification, Origin, Quantity)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """
        insert_params = (model.contract_no, model.picking_id, model.material_code,
                         model.brand, model.model, model.specification, model.origin, model.quantity)
        
        # 执行插入
        database.execute(insert_sql, insert_params)
        
        # 获取最后插入的ID
        id_query = "SELECT IDENT_CURRENT('TempBrandModels') AS ID"
        result = database.Query_one(id_query)
        
        # 获取生成的ID
        new_id = int(result[0]) if result else None
        
        return {
            "message": "数据保存成功", 
            "status": "success",
            "ID": new_id
        }

    except Exception as e:
        logging.error(f"保存临时品牌型号数据失败: {str(e)}")
        # 返回更详细的错误信息
        raise HTTPException(status_code=500, detail={"error": str(e), "model": model.dict()})


@router.get("/{contract_no}/{picking_id}", response_model=List[dict])
async def get_temp_brand_models(contract_no: str, picking_id: str):
    try:
        query_sql = """
            SELECT ID, ContractNo, PickingID, MaterialCode, Brand, Model, 
                   Specification, Origin, Quantity, CreatedAt, LastModified
            FROM TempBrandModels 
            WHERE ContractNo = ? AND PickingID = ?
        """
        
        rows = database.Query_all(query_sql, (contract_no, picking_id))
        
        # 转换结果为字典列表
        results = []
        for row in rows:
            result = {
                "ID": row[0],
                "ContractNo": row[1],
                "PickingID": row[2],
                "MaterialCode": row[3],
                "Brand": row[4],
                "Model": row[5],
                "Specification": row[6],
                "Origin": row[7] or "", # 处理NULL值
                "Quantity": row[8],
                "CreatedAt": row[9],
                "LastModified": row[10]
            }
            results.append(result)
            
        return results

    except Exception as e:
        print(f"获取临时品牌型号数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"获取临时品牌型号数据失败: {str(e)}")


@router.delete("/id/{id}", response_model=dict)
async def delete_temp_brand_model_by_id(id: int):
    try:
        delete_sql = "DELETE FROM TempBrandModels WHERE ID = ?"

        database.execute(delete_sql, (id,))

        return {"message": "临时数据删除成功", "status": "success"}

    except Exception as e:
        logging.error(f"删除临时品牌型号数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"删除临时品牌型号数据失败: {str(e)}")


@router.delete("/{contract_no}/{picking_id}", response_model=dict)
async def delete_temp_brand_models(contract_no: str, picking_id: str):
    try:
        delete_sql = """
            DELETE FROM TempBrandModels 
            WHERE ContractNo = ? AND PickingID = ?
        """
        
        database.execute(delete_sql, (contract_no, picking_id))
        
        return {"message": "临时数据删除成功", "status": "success"}

    except Exception as e:
        logging.error(f"删除临时品牌型号数据失败: {str(e)}")
        raise HTTPException(status_code=500, detail=f"删除临时品牌型号数据失败: {str(e)}")
